﻿-- ============================================================
-- MembershipRolePower Reference Data Synchronization Script
-- ============================================================

CREATE TABLE #MembershipRolePower
(
	[RoleId] [int] NOT NULL,
	[PowerId] [int] NOT NULL,
	[GetFlag] [bit] NOT NULL,
	[AddFlag] [bit] NOT NULL,
	[EditFlag] [bit] NOT NULL,
	[DelFlag] [bit] NOT NULL,
	[ExecFlag] [bit] NOT NULL
);

SET NOCOUNT ON;

-- Get Role relations
DECLARE
	@admin_role int, 
	@customer_role int,
	@guest_role int;
SELECT @admin_role = a.[Id] FROM [dbo].[MembershipRole] AS a WHERE a.[Name] = 'Administrators';
SELECT @customer_role = a.[Id] FROM [dbo].[MembershipRole] AS a WHERE a.[Name] = 'Customers';
SELECT @guest_role = a.[Id] FROM [dbo].[MembershipRole] AS a WHERE a.[Name] = 'Guests';

-- Get Power relations
DECLARE
	@MembershipUser int, 
	@MembershipRole int;
SELECT @MembershipUser = a.[Id] FROM [dbo].[MembershipPower] AS a WHERE a.[Name] = 'User';
SELECT @MembershipRole = a.[Id] FROM [dbo].[MembershipPower] AS a WHERE a.[Name] = 'Role';

-- Generate Data
INSERT #MembershipRolePower([RoleId],[PowerId],[GetFlag],[AddFlag],[EditFlag],[DelFlag],[ExecFlag]) VALUES ( @admin_role, @MembershipUser, 1, 1, 1, 1, 1)
INSERT #MembershipRolePower([RoleId],[PowerId],[GetFlag],[AddFlag],[EditFlag],[DelFlag],[ExecFlag]) VALUES ( @admin_role, @MembershipRole, 1, 1, 1, 1, 1)

MERGE [dbo].[MembershipRolePower] AS Target
	USING #MembershipRolePower AS Source ON (Target.[RoleId] = Source.[RoleId] AND Target.[PowerId] = Source.[PowerId])
		--WHEN MATCHED THEN 
		--	UPDATE SET Target.[GetFlag] = Source.[GetFlag]
		--			 , Target.[AddFlag] = Source.[AddFlag]
		--			 , Target.[EditFlag] = Source.[EditFlag]
		--			 , Target.[DelFlag] = Source.[DelFlag]
		--			 , Target.[ExecFlag] = Source.[ExecFlag]
		WHEN NOT MATCHED BY TARGET THEN
			INSERT ([RoleId], [PowerId], [GetFlag], [AddFlag], [EditFlag], [DelFlag], [ExecFlag])
			VALUES (Source.[RoleId], Source.[PowerId], Source.[GetFlag], Source.[AddFlag], Source.[EditFlag], Source.[DelFlag], Source.[ExecFlag]);

DROP TABLE #MembershipRolePower;

UPDATE MembershipRole SET
  [Desc] = [Desc]

UPDATE dbo.MembershipPower SET
  [Desc] = [Desc]


GO
